﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Collections;
using System.Threading;
using System.Data.Odbc;
using MySql.Data.MySqlClient;

using MyLibrary.Data.SqlServer;
using Tool = MyLibrary.Tools.CommonTools;

namespace MyLibrary.Data.DatabaseCollection
{
    /// <summary>
    /// 服务器连接集合类
    /// </summary>
    /// <typeparam name="Key"></typeparam>
    /// <typeparam name="Connection"></typeparam>
    public class ServerConnectionCollection<Key, Connection> : DictionaryBase where Connection : IDbConnection
    {

        private Dictionary<Key, string> _ConnetcionStringCollection = new Dictionary<Key, string>();
        /// <summary>
        /// 连接字符串集合
        /// </summary>
        public Dictionary<Key, string> ConnetcionStringCollection
        {
            get { return _ConnetcionStringCollection; }
            set { _ConnetcionStringCollection = value; }
        }

        public ServerConnectionCollection()
        {
            
        }

        public Connection this[Key index]
        {
            get { return (Connection)Dictionary[index]; }
            set { Dictionary[index] = value; }
        }

        /// <summary>
        /// 得到集合的键组
        /// </summary>
        public ICollection Keys
        {
            get { return this.Dictionary.Keys; }
        }

        #region Common Method
        /// <summary>
        /// 向集合中添加一个集合
        /// </summary>
        /// <param name="key">连接的唯一标识</param>
        /// <param name="connection">数据库连接对象</param>
        public void AddConnection(Key key, Connection connection)
        {
            if (!this.Dictionary.Contains(key))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                Dictionary.Add(key, connection);
            }
            else
                throw new System.ArgumentException("已经存在相同的键");
        }

        public void Clone(Key key, Key newkey)
        {
            Type type = Dictionary[key].GetType();
            object instance = Activator.CreateInstance(type);
        }

        /// <summary>
        /// 集合中是否包含指定键的连接
        /// </summary>
        /// <param name="key">键</param>
        /// <returns>是否包含</returns>
        public bool ContainsConnection(Key key)
        {
            return this.Dictionary.Contains(key);
        }
        /// <summary>
        /// 从集合中移除指定的连接
        /// </summary>
        /// <param name="key">连接的唯一标识</param>
        /// <exception cref="ArgumentNullException">队列中不存在需要移除的连接</exception>
        public void RemoveConncetion(Key key)
        {
            if (!this.Dictionary.Contains(key))
                throw new ArgumentNullException("key", "队列中不存在需要移除的连接");

            if (this[key].State != ConnectionState.Closed)
                this[key].Close();

            this[key].Dispose();
            this.Dictionary.Remove(key);
        }

        /// <summary>
        /// 从集合中移除所有的连接
        /// </summary>
        public void RemoveAllConnection()
        {
            if (this.Dictionary.Count == 0)
                throw new NotSupportedException("不支持清空空集合的方法");

            foreach (Connection conn in this.Dictionary.Values)
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
                conn.Dispose();
            }
            this.Dictionary.Clear();
        }

        /// <summary>
        /// 得到连接的源
        /// </summary>
        /// <param name="key">键</param>
        /// <returns>连接源</returns>
        public string GetConnectionSource(Key key)
        {
            if (!this.Dictionary.Contains(key)) throw new ArgumentException("没有找到对应键值的连接对象");
            IDbConnection connection = (IDbConnection)this.Dictionary[key];
            if (connection is OdbcConnection)
                return ((OdbcConnection)connection).DataSource;
            else if (connection is SqlConnection)
                return ((SqlConnection)connection).DataSource;
            else
                return String.Empty;
        }

        /// <summary>
        /// 得到连接的数据库
        /// </summary>
        /// <param name="key">键</param>
        /// <returns>数据库</returns>
        public string GetConnectionDatabase(Key key)
        {
            if (!this.Dictionary.Contains(key)) throw new ArgumentException("没有找到对应键值的连接对象");
            return ((IDbConnection)this.Dictionary[key]).Database;
        }
        #endregion

        #region ExecuteDataTable
        /// <summary>
        /// 执行SQL语句,返回执行结果数据集的第一个数据表
        /// </summary>
        /// <param name="key">使用的连接</param>
        /// <param name="sqlcommand">SQL指令</param>
        /// <param name="commandType">指令类型</param>
        /// <param name="parameters">参数</param>
        /// <returns>数据表</returns>
        public DataTable ExecuteDataTableInPool(Key key, string sqlcommand, CommandType commandType, params IDbDataParameter[] parameters)
        {
            DataTable returntable = null;
            try
            {
                if (((Connection)this.Dictionary[key]) is SqlConnection)
                {
                    returntable = SQLServerHelper.ExecuteDataset(((SqlConnection)this.Dictionary[key]),
                        commandType, sqlcommand, (SqlParameter[])parameters).Tables[0];
                }
                else if (((Connection)this.Dictionary[key]) is MySqlConnection)
                {
                    returntable = MySqlHelper.ExecuteDataSet(((MySqlConnection)this.Dictionary[key]), sqlcommand, (MySqlParameter[])parameters).Tables[0];
                }
                else if (((Connection)this.Dictionary[key]) is OdbcConnection)
                {
                    if (((OdbcConnection)this.Dictionary[key]).State != ConnectionState.Open)
                        ((OdbcConnection)this.Dictionary[key]).Open();
                    DataSet ds = new DataSet();
                    OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(sqlcommand, ((OdbcConnection)this.Dictionary[key]));
                    odbcAdapter.Fill(ds);
                    returntable = ds.Tables[0];
                    ((OdbcConnection)this.Dictionary[key]).Close();
                }
                return returntable;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 执行SQL语句,返回执行结果数据集的第一个数据表
        /// </summary>
        /// <param name="key">使用的连接</param>
        /// <param name="sqlcommand">SQL指令</param>
        /// <param name="commandType">指令类型</param>
        /// <returns>数据表</returns>
        public DataTable ExecuteDataTableInPool(Key key, string sqlcommand, CommandType commandType)
        {
            DataTable returntable = null;
            try
            {
                if (((Connection)this.Dictionary[key]) is SqlConnection)
                {
                    returntable = SQLServerHelper.ExecuteDataset(((SqlConnection)this.Dictionary[key]),
                        commandType, sqlcommand).Tables[0];
                }
                else if (((Connection)this.Dictionary[key]) is MySqlConnection)
                {
                    returntable = MySqlHelper.ExecuteDataSet(((MySqlConnection)this.Dictionary[key]), sqlcommand).Tables[0];
                }
                else if (((Connection)this.Dictionary[key]) is OdbcConnection)
                {
                    if (((OdbcConnection)this.Dictionary[key]).State != ConnectionState.Open)
                        ((OdbcConnection)this.Dictionary[key]).Open();
                    DataSet ds = new DataSet();
                    OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(sqlcommand, ((OdbcConnection)this.Dictionary[key]));
                    odbcAdapter.Fill(ds);
                    returntable = ds.Tables[0];
                    ((OdbcConnection)this.Dictionary[key]).Close();
                }
                return returntable;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region ExecuteDataReader
        /// <summary>
        /// 执行SQL语句,返回DataReader
        /// </summary>
        /// <param name="key">使用的连接</param>
        /// <param name="sqlcommand">SQL指令</param>
        /// <returns>DataReader</returns>
        public IDataReader ExecuteDataReaderInPool(Key key, string sqlcommand)
        {
            try
            {
                if (((Connection)this.Dictionary[key]) is SqlConnection)
                    return SQLServerHelper.ExecuteReader(((SqlConnection)this.Dictionary[key]), CommandType.Text, sqlcommand);
                else if (((Connection)this.Dictionary[key]) is MySqlConnection)
                    return MySqlHelper.ExecuteReader(((MySqlConnection)this.Dictionary[key]), sqlcommand);
                else
                    return null;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="key">使用的连接</param>
        /// <param name="sqlcommand">SQL指令</param>
        /// <returns>影响的行数</returns>
        public int ExecuteNonQueryInPool(Key key, string sqlcommand)
        {
            try
            {
                if (((Connection)this.Dictionary[key]) is SqlConnection)
                    return SQLServerHelper.ExecuteNonQuery(((SqlConnection)this.Dictionary[key]), CommandType.Text, sqlcommand);
                else if (((Connection)this.Dictionary[key]) is MySqlConnection)
                    return MySqlHelper.ExecuteNonQuery(((MySqlConnection)this.Dictionary[key]), sqlcommand);
                else
                    return -1;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 执行SQL语句|只能支持MSSQL
        /// </summary>
        /// <param name="key">使用的连接</param>
        /// <param name="sqlcommand">SQL指令</param>
        /// <returns>影响的行数</returns>
        public int ExecuteNonQueryInPool(Key key, string sqlcommand, CommandType commandType, params SqlParameter[] parameters)
        {
            try
            {
                if (((Connection)this.Dictionary[key]) is SqlConnection)
                    return SQLServerHelper.ExecuteNonQuery(((SqlConnection)this.Dictionary[key]), commandType, sqlcommand, parameters);
                else
                    return 0;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 在一个线程中执行SQL语句，不考虑返回值
        /// </summary>
        /// <param name="key"></param>
        /// <param name="sqlcommand"></param>
        /// <returns></returns>
        public void ExecuteNonQueryByThreadInPool(Key key, string sqlcommand)
        {
            Thread thread = new Thread(delegate()
            {
                ExecuteNonQueryInPool(key, sqlcommand);
            });
            thread.IsBackground = true;
            thread.Start();
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 执行SQL语句,返回执行的第一个结果
        /// </summary>
        /// <param name="key">使用的连接</param>
        /// <param name="sqlcommand">SQL指令</param>
        /// <returns>执行的第一个结果</returns>
        public object ExecuteScalarInPool(Key key, string sqlcommand)
        {
            try
            {
                if (((Connection)this.Dictionary[key]) is SqlConnection)
                    return SQLServerHelper.ExecuteScalar(((SqlConnection)this.Dictionary[key]), CommandType.Text, sqlcommand);
                else if (((Connection)this.Dictionary[key]) is MySqlConnection)
                    return MySqlHelper.ExecuteScalar(((MySqlConnection)this.Dictionary[key]), sqlcommand);
                else if (((Connection)this.Dictionary[key]) is OdbcConnection)
                {
                    OdbcCommand odbcCommand = new OdbcCommand(sqlcommand, ((OdbcConnection)this.Dictionary[key]));
                    if (odbcCommand.Connection.State != ConnectionState.Open)
                        odbcCommand.Connection.Open();
                    object retrunObj = odbcCommand.ExecuteScalar();
                    ((OdbcConnection)this.Dictionary[key]).Close();
                    return retrunObj;
                }
                else
                    return -1;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region RepairConnection
        /// <summary>
        /// 修复指定的连接
        /// </summary>
        /// <param name="key">连接的键</param>
        /// <param name="connectionString">连接字符串</param>
        public void RepairConnection(Key key, string serverType)
        {
            if (!this.Dictionary.Contains(key))
                return;
            if (!this._ConnetcionStringCollection.ContainsKey(key))
                return;

            IDbConnection connection = null;
            //如果存在需要的连接
            this.Dictionary.Remove(key);
            switch (serverType.ToLower())
            {
                case "mssql":
                    connection = new SqlConnection();
                    break;
                case "mysql":
                    connection = new MySqlConnection();
                    break;
                case "odbc":
                    connection = new OdbcConnection();
                    break;
            }
            connection.ConnectionString = this._ConnetcionStringCollection[key];
            Dictionary.Add(key, connection);
        }
        #endregion
    }
}
